﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using ConnectLib.DAO;
using MSSQLProvider.DataBase;

namespace ConnectLib.DAL
{
    public class CustomersDAL : MSSQLDataAccess
    {
        private static CustomersDAL _instance = new CustomersDAL();

        public static CustomersDAL Instance
        {
            get
            {
                return _instance;
            }
        }

        public CustomersDAL()
        {
        }

        public int Add(CustomersDAO cust)
        {
            try
            {
                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("Name");
                arrParamName.Add("@Name");
                arrValue.Add(cust.Name);

                arrFielName.Add("Address");
                arrParamName.Add("@Address");
                arrValue.Add(cust.Address);

                arrFielName.Add("Phone");
                arrParamName.Add("@Phone");
                arrValue.Add(cust.Phone);

                arrFielName.Add("TaxCode");
                arrParamName.Add("@TaxCode");
                arrValue.Add(cust.TaxCode);

                var queryBuider = new StringBuilder();
                queryBuider.Append("INSERT INTO [Customers] (");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    queryBuider.Append(arrFielName[i]);
                    if (i < arrFielName.Count - 1)
                        queryBuider.Append(",");
                }
                queryBuider.Append(")");
                queryBuider.Append(" VALUES (");
                i = 0;

                for (; i < arrParamName.Count; i++)
                {
                    queryBuider.Append(arrParamName[i]);
                    if (i < arrParamName.Count - 1)
                        queryBuider.Append(",");
                }
                queryBuider.Append(")");

                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int Update(CustomersDAO cust, int oldbranchid)
        {
            try
            {
                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("Name");
                arrParamName.Add("@Name");
                arrValue.Add(cust.Name);

                arrFielName.Add("Address");
                arrParamName.Add("@Address");
                arrValue.Add(cust.Address);

                arrFielName.Add("Phone");
                arrParamName.Add("@Phone");
                arrValue.Add(cust.Phone);

                arrFielName.Add("TaxCode");
                arrParamName.Add("@TaxCode");
                arrValue.Add(cust.TaxCode);

                arrFielName.Add("Id");
                arrParamName.Add("@Id");
                arrValue.Add(cust.Id);

                var queryBuider = new StringBuilder();
                queryBuider.Append("UPDATE [Customers] SET ");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    if (i != arrFielName.Count - 1)
                    {
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                        if (i < arrFielName.Count - 2)
                            queryBuider.Append(",");
                    }
                    else
                    {
                        queryBuider.Append(" WHERE ");
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                    }
                }

                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<CustomersDAO> GetCustomers()
        {
            List<CustomersDAO> custs = null;

            try
            {
                List<Hashtable> htbs = ExecuteArrayHastable("SELECT * FROM [Customers] WITH (NOLOCK)");

                if (htbs != null && htbs.Count > 0)
                {
                    custs = new List<CustomersDAO>(htbs.Count);
                    foreach (Hashtable htitem in htbs)
                    {
                        custs.Add(new CustomersDAO(htitem));
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return custs;
        }
    }
}
